package top.btcmarket.dbutils;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import static org.junit.Assert.*;

public class ConnTest {
	
	private static final String url = "jdbc:mysql://localhost:3306/chips?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&useSSL=true&allowMultiQueries=true";
	private static final String root = "root";
	private static final String pwd = "root";
	

	/*
	 * 新增Visitor, ScalarHandler的demo
	 */
	@Test
	public static void insertVisitor(Visitor visitor) {
		
		
	    Connection conn = DBUtil.getConnection(url, root, pwd);
	    QueryRunner qr = new QueryRunner();
	    String sql = "insert into visitor (Name, Email, Status, CreateTime) values (?, ?, ?, ?)";
	    try {
	        int count = qr.update(conn, sql, visitor.getName(), visitor.getEmail(), 1, new Date());
	        BigInteger newId = (BigInteger) qr.query(conn, "select last_insert_id()", new ScalarHandler<BigInteger>(1));
	        visitor.setId(Integer.valueOf(String.valueOf(newId)));
	        System.out.println("新增" + count + "条数据=>Id：" + newId);
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }finally{
	    	if(conn != null){
	    		try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	    	}
	    }
	}
	@Test
	public static void deleteVisitor(int id) {
	    Connection conn = DBUtil.getConnection(url, root, pwd);
	    QueryRunner qr = new QueryRunner();
	    String sql = "delete from visitor where status>0 and id=?";
	    try {
	        int count = qr.update(conn, sql, id);
	        System.out.println("删除" + count + "条数据。");
	    } catch (SQLException e) {
	        // TODO: handle exception
	        e.printStackTrace();
	    }
	}
	@Test
	public static Visitor retrieveVisitor(int id) {
	    Connection conn = DBUtil.getConnection(url, root, pwd);
	    Visitor visitor = null;
	    QueryRunner qr = new QueryRunner();
	    String sql = "select * from visitor where status>0 and id=?";        
	    try {
	        visitor = (Visitor) qr.query(conn, sql, new BeanHandler<Visitor>(Visitor.class), id);
	        System.out.println(visitor);
	        return visitor;
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return visitor;
	}
	@Test
	public static void updateVisitor(int id) {
	    Visitor visitor = retrieveVisitor(id);
	    System.out.println("更新前：" + visitor);
	    Connection conn = DBUtil.getConnection(url, root, pwd);
	    String updateFieldStr = visitor.getName();
	    QueryRunner qr = new QueryRunner();
	    String sql = "update visitor set Name = ?, Email = ?, Status = ?, CreateTime = ? where status>0 and Id = ?";
	    if (updateFieldStr.contains("updated")) {
	        updateFieldStr = updateFieldStr.substring(0, updateFieldStr.indexOf("updated"));
	    } else {
	        updateFieldStr = updateFieldStr + "updated";
	    }
	    visitor.setName(updateFieldStr);
	    try {
	        int count = qr.update(conn, sql, new Object[] { visitor.getName(), visitor.getName(), visitor.getStatus(),
	                visitor.getCreateTime(), visitor.getId() });
	        System.out.println("更新了" + count + "条数据");
	        System.out.println("更新后：" + visitor);
	    } catch (SQLException e) {
	        // TODO: handle exception
	        e.printStackTrace();
	    }
	}
	@Test
	public static void getVisitorList() {
	    Connection conn = DBUtil.getConnection(url, root, pwd);
	    QueryRunner qr = new QueryRunner();
	    String sql = "select * from visitor where status>0";
	    try {
	        List<Visitor> ls = qr.query(conn, sql, new BeanListHandler<Visitor>(Visitor.class));
	        for (Visitor visitor : ls) {
	            System.out.println(visitor);
	        }
	    } catch (SQLException e) {
	        // TODO Auto-generated catch block
	        e.printStackTrace();
	    }
	}
	@Test
	public static void getVisitWithMap(int id) {
	    Connection conn = DBUtil.getConnection(url, root, pwd);
	    QueryRunner qr = new QueryRunner();
	    String sql = "select * from visitor where status>0 and id=?";
	    try {
	        Map<String, Object> map = qr.query(conn, sql, new MapHandler(), id);
	        Integer visitorId = Integer.valueOf(map.get("Id").toString());
	        String visitorName = map.get("Name").toString();
	        String visitorEmail = map.get("Email").toString();
	        Integer visitorStatus = Integer.valueOf(map.get("Status").toString());
	        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	        Date visitorCreateTime = sdf.parse(map.get("CreateTime").toString());
	        Visitor visitor = new Visitor(visitorName, visitorEmail);
	        visitor.setId(visitorId);
	        visitor.setStatus(visitorStatus);
	        visitor.setCreateTime(visitorCreateTime);
	        System.out.println(visitor);
	    } catch (Exception e) {
	        // TODO: handle exception
	        e.printStackTrace();
	    }
	}
	@Test
	public static void getVisitWithMapLs() {
        Connection conn = DBUtil.getConnection(url, root, pwd);
        QueryRunner qr = new QueryRunner();
        String sql = "select * from visitor where status>0";
        try {
            List<Map<String, Object>> mapLs = qr.query(conn, sql, new MapListHandler());
            for (Map<String, Object> map : mapLs) {
                Integer visitorId = Integer.valueOf(map.get("Id").toString());
                String visitorName = map.get("Name").toString();
                String visitorEmail = map.get("Email").toString();
                Integer visitorStatus = Integer.valueOf(map.get("Status").toString());
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date visitorCreateTime = sdf.parse(map.get("CreateTime").toString());
                Visitor visitor = new Visitor(visitorName, visitorEmail);
                visitor.setId(visitorId);
                visitor.setStatus(visitorStatus);
                visitor.setCreateTime(visitorCreateTime);
                System.out.println(visitor);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }
	
}
